<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

function mysql_to_array($path)
{
    
//    $path = FCPATH . 'uploads/database/flat.sql';
    
    $fields = array();
    $db_lines = file($path);
    $modules = array();

    foreach( $db_lines as $key => $line )
    {
        $line = trim($line);
        $opening = strpos($line, '`');
        $closing = strrpos($line, '`');

        // create table
        if( strpos($line, 'CREATE TABLE') !== false )
        {
            $table_name = substr($line, $opening + 1, $closing - strlen($line));
            $foreign_keys = array();
            $fields = array();
            $params = array();
            for( $i = $key; $i < count($db_lines); $i++ )
            {
                $current_line = trim( $db_lines[$i] );
                if( strpos($current_line, ';') !== false )
                {
                    break;
                } else
                {
                    $opening_quot = strpos($current_line, '`');
                    $closing_quot = strrpos($current_line, '`');
                    $opening_parenthesis = strpos($current_line, '(');
                    $closing_parenthesis = strrpos($current_line, ')');

                    if( strpos($current_line, 'PRIMARY KEY') !== false )
                    {
                        $params['primary_key'] = substr($current_line, $opening_quot + 1, $closing_quot - strlen($current_line));
                    }
                    else if( strpos($current_line, 'CONSTRAINT') !== false )
                    {
                        $constraints = explode(' ', $current_line);
                        $key_idx = array_search('KEY', $constraints);
                        $key_name = substr( $constraints[$key_idx + 1], 2, -2 );
                        $reference_idx = array_search('REFERENCES', $constraints);
                        $delete_idx = array_search('DELETE', $constraints);
                        $update_idx = array_search('UPDATE', $constraints);

                        $foreign_meta = array();
                        $foreign_meta['key'] = $key_name;

                        if( substr( $constraints[$reference_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['reference'] = substr( $constraints[$reference_idx + 1], 1, -2 );
                        } else
                        {
                            $foreign_meta['reference'] = substr( $constraints[$reference_idx + 1], 1, -1 );
                        }
                        if( substr( $constraints[$delete_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['delete'] = substr( $constraints[$delete_idx + 1], 0, -1 );
                        } else
                        {
                            $foreign_meta['delete'] = $constraints[$delete_idx + 1];
                        }
                        if( substr( $constraints[$update_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['update'] = substr( $constraints[$update_idx + 1], 0, -1 );
                        } else
                        {
                            $foreign_meta['update'] = $constraints[$update_idx + 1];
                        }
                        $foreign_keys[$key_name] = $foreign_meta;
                        $params['foreign_keys'] = $foreign_keys;
                    }
                    else if( (strpos($current_line, '`') !== false) && !(strpos($current_line, 'CREATE TABLE') !== false) && !(strpos($current_line, 'KEY') !== false) && !(strpos($current_line, 'CONSTRAINT') !== false) && !(strpos($current_line, ';') !== false)) // check if field data
                    {
                        $field_meta = array();
                        $attribs = explode(' ', trim($current_line) );

                        $field_meta['name'] = substr($attribs[0], 1, -1);

                        if (substr($attribs[1], -1) === ',')
                        {
                            $field_meta['type'] = substr($attribs[1], 0, -1);
                        } else
                        {
                            $field_meta['type'] = $attribs[1];
                        }

                        if (strpos($current_line, 'NOT NULL') !== false) // required
                        {
                            $field_meta['required'] = true;
                        }

                        // default value
                        if (strpos($current_line, 'DEFAULT') !== false)
                        {
                            $idx = array_search('DEFAULT', $attribs);
                            if (substr($attribs[$idx + 1], -1) === ',')
                            {
                                $field_meta['default_value'] = substr($attribs[$idx + 1], 0, -1);
                            } else
                            {
                                $field_meta['default_value'] = $attribs[$idx + 1];
                            }
                        }

                        // int values
                        if (strpos($attribs[1], 'int') !== false)
                        {
                            $opening_parenthesis = strpos($attribs[1], '(');
                            $closing_parenthesis = strrpos($attribs[1], ')');

                            if (strpos($attribs[1], 'tinyint') !== false) // if tiny int
                            {
                                $field_meta['type'] = 'tinyint';
                            }
                            if (strpos($attribs[1], 'smallint') !== false) // if small int
                            {
                                $field_meta['type'] = 'smallint';
                            }
                            if (strpos($attribs[1], 'medium') !== false) // if medium int
                            {
                                $field_meta['type'] = 'medium';
                            }
                            if (strpos($attribs[1], 'bigint') !== false) // if big int
                            {
                                $field_meta['type'] = 'bigint';
                            }
                            else
                            {
                                $field_meta['type'] = 'int';
                            }
                            $field_meta['max_length'] = substr($attribs[1], $opening_parenthesis + 1, $closing_parenthesis - strlen($attribs[1]));
                        }

                        // character values
                        if (strpos($attribs[1], 'char') !== false)
                        {
                            $opening_parenthesis = strpos($attribs[1], '(');
                            $closing_parenthesis = strrpos($attribs[1], ')');

                            if (strpos($attribs[1], 'varchar') !== false) // if varchar
                            {
                                $field_meta['type'] = 'varchar';
                            }
                            else
                            {
                                $field_meta['type'] = 'char';
                            }
                            $field_meta['max_length'] = substr($attribs[1], $opening_parenthesis + 1, $closing_parenthesis - strlen($attribs[1]));
                        }


                        if (strpos($attribs[1], 'bit') !== false) // bit
                        {
                            $field_meta['type'] = 'bit';
                        }
                        if (strpos($attribs[1], 'binary') !== false) // binary
                        {
                            $field_meta['type'] = 'binary';
                        }
                        if (strpos($attribs[1], 'decimal') !== false) // binary
                        {
                            $field_meta['type'] = 'decimal';
                        }
                        if (strpos($attribs[1], 'enum') !== false || strpos($attribs[1], 'set') !== false) // enum and sets
                        {
                            $opening_parenthesis = strpos($attribs[1], '(');
                            $closing_parenthesis = strrpos($attribs[1], ')');

                            if (strpos($attribs[1], 'enum') !== false)
                            {
                                $field_meta['type'] = 'enum';
                            }
                            else
                            {
                                $field_meta['type'] = 'set';
                            }
                            $field_meta['set'] = substr($attribs[1], $opening_parenthesis + 1, $closing_parenthesis - strlen($attribs[1]));
                        }

                        $attribute_name = substr($current_line, $opening_quot + 1, $closing_quot - strlen($current_line));
                        $fields[$attribute_name] = $field_meta;

                        $params['fields'] = $fields;
                    }
                }
            }
            $modules[$table_name] = $params;
        }



        // alter table
        if( strpos($line, 'ALTER TABLE') !== false )
        {
            $table_name = substr($line, $opening + 1, $closing - strlen($line));
            $temp_module = $modules[$table_name];
            $foreign_keys = array();
            for( $i = $key; $i < count($db_lines); $i++ )
            {
                $current_line = trim($db_lines[$i]);
                if( strpos($current_line, ';') !== false )
                {
                    if( strpos($current_line, 'ADD CONSTRAINT') !== false )
                    {
                        $constraints = explode(' ', $current_line);
                        $key_idx = array_search('KEY', $constraints);
                        $key_name = substr( $constraints[$key_idx + 1], 2, -2 );
                        $reference_idx = array_search('REFERENCES', $constraints);
                        $delete_idx = array_search('DELETE', $constraints);
                        $update_idx = array_search('UPDATE', $constraints);

                        $foreign_meta = array();
                        $foreign_meta['key'] = $key_name;

                        if( substr( $constraints[$reference_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['reference'] = substr( $constraints[$reference_idx + 1], 1, -2 );
                        } else
                        {
                            $foreign_meta['reference'] = substr( $constraints[$reference_idx + 1], 1, -1 );
                        }
                        if( substr( $constraints[$delete_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['delete'] = substr( $constraints[$delete_idx + 1], 0, -1 );
                        } else
                        {
                            $foreign_meta['delete'] = $constraints[$delete_idx + 1];
                        }
                        if( substr( $constraints[$update_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['update'] = substr( $constraints[$update_idx + 1], 0, -1 );
                        } else
                        {
                            $foreign_meta['update'] = $constraints[$update_idx + 1];
                        }
                        $foreign_keys[$key_name] = $foreign_meta;
                        $temp_module['foreign_keys'] = $foreign_keys;
                    }
                    break;
                } else
                {
                    if( strpos($current_line, 'ADD CONSTRAINT') !== false )
                    {
                        $constraints = explode(' ', $current_line);
                        $key_idx = array_search('KEY', $constraints);
                        $key_name = substr( $constraints[$key_idx + 1], 2, -2 );
                        $reference_idx = array_search('REFERENCES', $constraints);
                        $delete_idx = array_search('DELETE', $constraints);
                        $update_idx = array_search('UPDATE', $constraints);

                        $foreign_meta = array();
                        $foreign_meta['key'] = $key_name;

                        if( substr( $constraints[$reference_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['reference'] = substr( $constraints[$reference_idx + 1], 1, -2 );
                        } else
                        {
                            $foreign_meta['reference'] = substr( $constraints[$reference_idx + 1], 1, -1 );
                        }
                        if( substr( $constraints[$delete_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['delete'] = substr( $constraints[$delete_idx + 1], 0, -1 );
                        } else
                        {
                            $foreign_meta['delete'] = $constraints[$delete_idx + 1];
                        }
                        if( substr( $constraints[$update_idx + 1], -1 ) === ',')
                        {
                            $foreign_meta['update'] = substr( $constraints[$update_idx + 1], 0, -1 );
                        } else
                        {
                            $foreign_meta['update'] = $constraints[$update_idx + 1];
                        }
                        $foreign_keys[$key_name] = $foreign_meta;
                        $temp_module['foreign_keys'] = $foreign_keys;
                    }
                }
            }
            $modules[$table_name] = $temp_module;
        }
    }
    return $modules;  
}

/* End of file mysql_parser_helper.php */
/* Location: ./application/helpers/mysql_parser_helper.php */